package com.css.fxfzcpxx.modules.courseManage.repository.impl;

import com.css.fxfzcpxx.constants.YNEnum;
import com.css.fxfzcpxx.modules.categoryView.param.QueParam;
import com.css.fxfzcpxx.modules.categoryView.vo.CategoryViewVo;
import com.css.fxfzcpxx.modules.courseManage.entity.CourseManage;
import com.css.fxfzcpxx.modules.courseManage.entity.TestPaperTemplate;
import com.css.fxfzcpxx.modules.courseManage.entity.TestPaperTemplateRule;
import com.css.fxfzcpxx.modules.courseManage.param.CourseManageParam;
import com.css.fxfzcpxx.modules.courseManage.repository.CourseManageRepository;
import com.css.fxfzcpxx.modules.courseManage.vo.HistoryTestVO;
import com.css.fxfzcpxx.modules.courseManage.vo.OnlineCourseVO;
import com.css.fxfzcpxx.modules.courseManage.vo.TypeManageNumVO;
import com.css.fxfzcpxx.modules.questionData.entity.UserTestHistory;
import com.css.fxfzcpxx.modules.questionData.vo.FractionalCurveVO;
import com.css.fxfzcpxx.modules.questionData.vo.UserTestHistoryVO;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/03/08
 * @Description: 在线课堂管理
 */
@Repository
public class CourseManageRepositoryImpl implements CourseManageRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static final String TABLE_NAME = "cpxx_course_manage";

    private static final String TABLE_ID = "id"; // 主键id

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, CourseManageParam param) {
        StringBuilder sql = new StringBuilder("select a.*,b.course_type from (select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by a.create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        String limitSql = String.format(" limit %d  offset %d ", pageSize, firstIndex);
        //时间范围
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & !PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and Date(release_date) between '" + param.getStartTime() + "' and '" + param.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(param.getStartTime()) & PlatformObjectUtils.isEmpty(param.getEndTime())) {
            whereSql.append("and Date(release_date)  >='" + param.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(param.getEndTime()) & PlatformObjectUtils.isEmpty(param.getStartTime())) {
            whereSql.append("and Date(release_date)  <='" + param.getEndTime() + "'");
        }
        //课程名称
        if (!PlatformObjectUtils.isEmpty(param.getCourseName())) {
            whereSql.append(" and course_name like '%" + param.getCourseName() + "%'");
        }
        //课程类别
        if (!PlatformObjectUtils.isEmpty(param.getCourseTypeId())) {
            whereSql.append(" and course_type_id = '" + param.getCourseTypeId() + "'");
        }
        //课堂练习 0有1没有
        if (!PlatformObjectUtils.isEmpty(param.getExercise())) {
            whereSql.append(" and exercise = '" + param.getExercise() + "'");
        }
        //发布状态 0发布1没有发布
        if (!PlatformObjectUtils.isEmpty(param.getReleaseState())) {
            whereSql.append(" and release_state = '" + param.getReleaseState() + "'");
        }
        StringBuilder sqlb = new StringBuilder(" )a left join (select id,type_name as course_type from cpxx_type_manage where del_flag = '0') b on a.course_type_id = b.id ");
        sql.append(whereSql).append(sqlb).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CourseManage.class)).size();
        List<CourseManage> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(CourseManage.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //保存在线课堂管理
    @Override
    public void save(CourseManage courseManage) {
        String sql = "insert into "+TABLE_NAME+"(id,course_name,course_type_id,release_date,exercise_id," +
                "exercise,release_state,course_file,download,cover,course_introduction,note,del_flag,create_user,create_time) " +
                "values(:id,:courseName,:courseTypeId,:releaseDate,:exerciseId," +
                ":exercise,:releaseState,:courseFile,:download,:cover,:courseIntroduction,:note,:delFlag,:createUser, :createTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(courseManage);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //保存试卷模板
    @Override
    public void saveTestPaperTemplate(TestPaperTemplate testPaperTemplate) {
        String sql = "insert into cpxx_test_paper_template (id,exercise_number,exercise_score,del_flag,create_user,create_time) " +
                "values(:id,:exerciseNumber,:exerciseScore,:delFlag,:createUser, :createTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(testPaperTemplate);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //保存试卷模板规则
    @Override
    public void saveTestPaperTemplateRule(TestPaperTemplateRule testPaperTemplateRule) {
        String sql = "insert into cpxx_test_paper_template_rule (id,template_id,type_id,single_option,single_option_score," +
                "multiple_selection,multiple_selection_score,judgment,judgment_score,del_flag,create_user,create_time) " +
                "values(:id,:templateId,:typeId,:singleOption,:singleOptionScore," +
                ":multipleSelection,:multipleSelectionScore,:judgment,:judgmentScore,:delFlag,:createUser, :createTime)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(testPaperTemplateRule);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //更改发布状态
    @Override
    public void updateReleaseStatusById(List<String> id, Date date, String toString) {
        String sql = "update " + TABLE_NAME + " set release_state = '" + toString + "',release_date = '"+date+"'  where " + TABLE_ID + " in  ('" + String.join("','", id) + "')";
        jdbcTemplate.update(sql);
    }

    //根据ids查询所以试卷模板id
    @Override
    public List<String> queryExerciseId(String ids) {
        String sql = "select exercise_id from "+ TABLE_NAME +" where "+ TABLE_ID + " in  ('" + String.join("','", ids) + "')";
        List<String> list = jdbcTemplate.queryForList(sql,String.class);
        return list;
    }

    //删除在线课堂
    @Override
    public void delete(List<String> asList) {
        String sql = "update "+TABLE_NAME+" set del_flag = '"+YNEnum.Y.toString()+"' where id in  ('" + String.join("','", asList) + "')";
        jdbcTemplate.update(sql);
    }
    //删除在线课堂对应模板
    @Override
    public void deleteTemplate(List<String> list) {
        String sql = "update cpxx_test_paper_template set del_flag = '"+YNEnum.Y.toString()+"' where id in  ('" + String.join("','", list) + "')";
        jdbcTemplate.update(sql);
    }
    //删除对应模板规则
    @Override
    public void deleteTemplateRule(List<String> list) {
        String sql = "update cpxx_test_paper_template_rule set del_flag = '"+YNEnum.Y.toString()+"' where id in  ('" + String.join("','", list) + "')";
        jdbcTemplate.update(sql);
    }

    //根据ids查询对应数据
    @Override
    public List<CourseManage> queryByParam(List<String> asList) {
        String sql = "select a.*,b.course_type from (select * from "+TABLE_NAME+" where "+TABLE_ID+" in  ('" + String.join("','", asList) + "') )a left join (select id,type_name as course_type from cpxx_type_manage where del_flag = '0') b on a.course_type_id = b.id ";
        List<CourseManage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CourseManage.class));
        return list;
    }

    @Override
    public void upDateTestPaperTemplate(TestPaperTemplate param) {
        String sql = "update cpxx_test_paper_template " +
                " set exercise_number = :exerciseNumber,exercise_score = :exerciseScore," +
                "update_user = :updateUser,update_time = :updateTime " +
                "where del_flag = '0' and " + TABLE_ID + " = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void upDateTestPaperTemplateRule(TestPaperTemplateRule param) {
        String sql = "update cpxx_test_paper_template_rule " +
                " set type_id = :typeId,single_option = :singleOption, " +
                "single_option_score = :singleOptionScore,multiple_selection = :multipleSelection, " +
                "multiple_selection_score = :multipleSelectionScore,judgment = :judgment, " +
                "judgment_score = :judgmentScore,"+
                "update_user = :updateUser,update_time = :updateTime " +
                "where del_flag = '0' and " + TABLE_ID + " = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void updateById(CourseManage param) {
        String sql = "update "+TABLE_NAME+
                " set course_name = :courseName,course_type_id=:courseTypeId, " +
                "exercise_id = :exerciseId,exercise = :exercise, " +
                "course_file = :courseFile,download = :download, " +
                "cover = :cover,course_introduction = :courseIntroduction,note=:note,"+
                "update_user = :updateUser,update_time = :updateTime " +
                "where del_flag = '0' and " + TABLE_ID + " = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //根据id查询在线课程
    @Override
    public CourseManage findById(String id) {
        String sql = "select a.*,b.course_type from (select * from " + TABLE_NAME + " where " + TABLE_ID + " = '" + id + "' )a left join (select id,type_name as course_type from cpxx_type_manage where del_flag = '0') b on a.course_type_id = b.id";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CourseManage.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //根据id查询考试模板
    @Override
    public TestPaperTemplate findTemplateById(String exerciseId) {
        String sql = "select * from cpxx_test_paper_template where " + TABLE_ID + " = '" + exerciseId + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(TestPaperTemplate.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //根据模板id查询试卷模规则
    @Override
    public List<TestPaperTemplateRule> findTemplateRuleById(String exerciseId) {
        String sql = "select * from cpxx_test_paper_template_rule where del_flag = '0' and template_id ='"+exerciseId+"'";
        List<TestPaperTemplateRule> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TestPaperTemplateRule.class));
        return list;
    }

    //根据试卷模板id批量删除试卷模板与试卷模板规则
    @Override
    public void deleteTemplateAndRule(String id) {
        String sql1 ="delete from cpxx_test_paper_template where id ='"+id+"'";
        jdbcTemplate.update(sql1);
        String sql2 ="delete from cpxx_test_paper_template_rule where template_id ='"+id+"'";
        jdbcTemplate.update(sql2);
    }

    @Override
    public List<TypeManageNumVO> getTypeNum(String typeName) {
        StringBuilder sqla = new StringBuilder("select b.id,b.type_name,b.type_plate,a.num,a.question_type from (select * from cpxx_type_manage");
        StringBuilder whereSql = new StringBuilder(" where del_flag='0' and type_plate = '题库' ");
        //类别名称
        if ( !PlatformObjectUtils.isEmpty(typeName)) {
            whereSql.append("and type_name  like'%" + typeName + "%'");
        }
        StringBuilder sqlb = new StringBuilder(  ")b " +
                "left join " +
                "(SELECT count(type_id) as num ,question_type,type_id FROM cpxx_question_data where del_flag='0' group by type_id ,question_type) a " +
                "on a.type_id=b.id");
        sqla.append(whereSql).append(sqlb);
        List<TypeManageNumVO> list = jdbcTemplate.query(sqla.toString(), new BeanPropertyRowMapper<>(TypeManageNumVO.class));
        return list;
    }

    @Override
    public List<HistoryTestVO> getHistoryTest(String id) {
        String sql = "select b.*,a.user_answer,a.correct from  " +
                "(select question_id,user_answer,correct,sequence from cpxx_user_tset_question where test_id = '"+id+"' ORDER BY sequence asc) a " +
                " left join " +
                "(select id,question_type,question_name,a,b,c,d,e,f,answers,analysis from cpxx_question_data where del_flag ='0') b " +
                " on " +
                "a.question_id=b.id";
        List<HistoryTestVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HistoryTestVO.class));
        return list;
    }

    //线上在线课堂滚动查询
    @Override
    public Map<String, Object> getOnlinePage(int curPage, int pageSize, String userID) {
        StringBuilder sql = new StringBuilder("select * from " +
                "(select * from cpxx_course_manage where del_flag ='0' and release_state ='0' )a " +
                "left join " +
                "(select course_id from cpxx_user_test_history where del_flag ='0' and create_user = '"+userID+"' group by course_id)b " +
                "on a.id=b.course_id");
        StringBuilder orderSql = new StringBuilder(" order by a.release_date desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(OnlineCourseVO.class)).size();
        List<OnlineCourseVO> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(OnlineCourseVO.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<UserTestHistory> getTestListByCourseId(String courseId,String userID) {
        String sql = "select * from  cpxx_user_test_history where del_flag ='0' and  course_id='"+courseId+"' and create_user = '"+userID+"' order by create_time desc ";
        List<UserTestHistory> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserTestHistory.class));
        return list;
    }

    //查询排行
    @Override
    public List<UserTestHistoryVO> getRanking() {
        String sql = "SELECT sum(score)as score_sun ,ROUND(avg(score),2)as score_avg,count(id)as num ,count(course_id)as time,create_name as create_user FROM cpxx_user_test_history group by create_name order by num desc";
        List<UserTestHistoryVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserTestHistoryVO.class));
        return list;
    }

    @Override
    public List<FractionalCurveVO> getFractionalCurve() {
        String sql = " select a.score_avg,count(a.score_avg)as num from " +
                " (select ROUND(avg(a.score_avg),0)as score_avg  from  " +
                " (select round(cast(score as numeric ),2) as score_avg,create_user " +
                " from cpxx_user_test_history) a " +
                " group by create_user)a " +
                " group by score_avg ORDER BY score_avg asc";
        List<FractionalCurveVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FractionalCurveVO.class));
        return list;
    }

    @Override
    public String getUserScore(String userID) {
        String sql = "select ROUND(avg(a.score_avg),0)as score_avg  from  " +
                "     (select round(cast(score as numeric ),2) as score_avg,create_user " +
                "     from cpxx_user_test_history  where create_user = '"+userID+"' ) a " +
                "     group by create_user ";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    //根据题库类型id查询题库里面各个题目类型(多选,单选,判断等)的数量
    @Override
    public List<TypeManageNumVO> getTypeNumByTypeId(String typeId) {
        StringBuilder sqla = new StringBuilder("select b.id,b.type_name,b.type_plate,a.num,a.question_type from (select * from cpxx_type_manage");
        StringBuilder whereSql = new StringBuilder(" where del_flag='0' ");
        //类别名称
        if ( !PlatformObjectUtils.isEmpty(typeId)) {
            whereSql.append("and id  ='" + typeId + "'");
        }
        StringBuilder sqlb = new StringBuilder(  ")b " +
                "left join " +
                "(SELECT count(type_id) as num ,question_type,type_id FROM cpxx_question_data where del_flag='0' group by type_id ,question_type) a " +
                "on a.type_id=b.id");
        sqla.append(whereSql).append(sqlb);
        List<TypeManageNumVO> list = jdbcTemplate.query(sqla.toString(), new BeanPropertyRowMapper<>(TypeManageNumVO.class));
        return list;
    }

    @Override
    public List<CategoryViewVo> getData(QueParam queParam) {
        StringBuilder sql = new StringBuilder("select course_name meansName,release_date publishingDate,create_time from " + TABLE_NAME+"");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");


        //时间范围
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & !PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date between '" + queParam.getStartTime() + "' and '" + queParam.getEndTime() + "'");
        }
        //大于开始时间
        if (!PlatformObjectUtils.isEmpty(queParam.getStartTime()) & PlatformObjectUtils.isEmpty(queParam.getEndTime())) {
            whereSql.append("and release_date  >='" + queParam.getStartTime() + "'");
        }
        //小于结束时间
        if ( !PlatformObjectUtils.isEmpty(queParam.getEndTime()) & PlatformObjectUtils.isEmpty(queParam.getStartTime())) {
            whereSql.append("and release_date  <='" + queParam.getEndTime() + "'");
        }
        //课程名称
        if (!PlatformObjectUtils.isEmpty(queParam.getKeyword())) {
            whereSql.append(" and course_name like '%" + queParam.getKeyword() + "%'");
        }

        /*if(queParam.getSortOrNot()){
            sql.append(whereSql).append(" order by create_time desc");
        }else{
            sql.append(whereSql).append(" order by release_date desc");
        }*/

        List<CategoryViewVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CategoryViewVo.class));

        return list;

    }

    @Override
    public Integer getDownload(String unit) {
        String sql = "select sum(b.download_count) from "+TABLE_NAME+" a  " +
                "inner join  sys_attachment_record b on a.course_file=b.attachment_id " +
                "where a.del_flag='0' and EXTRACT("+unit+" FROM b.download_time) = EXTRACT("+unit+" FROM CURRENT_DATE) ";
        Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
        if(PlatformObjectUtils.isEmpty(total)){
            return 0;
        }
        return total;
    }
}
